package dao.implementations.postgresql.Alumno;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.implementations.DBBase;
import dao.interfaces.Alumno.IAlumno;
import dao.transferObjects.Gae.AlumnoTO;
import dao.transferObjects.Gae.CursoTO;
import dao.transferObjects.Gae.GaeTO;

public class AlumnoDB extends DBBase implements IAlumno {
	
	private static String CAMPOS_ALUMNO =   " matricula, id_curso, id_gae, rut_gae, nombre, apellido, tipo_fono, "+ 
										    " fono, correo, nota1, nota2, nota3, nota_socio, auto_evaluacion, "+ 
										    " nota_final, eliminado "; 
	
	public AlumnoDB(Connection connection){
		this.conn = connection;
	}
	
	public AlumnoTO resultSet_a_AlumnoTO(ResultSet rs)
	{
		AlumnoTO alumno = new AlumnoTO();
		
		try{
			alumno.setMatricula(rs.getInt("matricula"));
			alumno.setId_curso(rs.getInt("id_curso"));
			alumno.setId_gae(rs.getInt("id_gae"));
			alumno.setRut_gae(rs.getInt("rut_gae"));
			alumno.setNombre(rs.getString("nombre"));
			alumno.setApellido(rs.getString("apellido"));
			alumno.setTipo_fono(rs.getString("tipo_fono"));
			alumno.setFono(rs.getInt("fono"));
			alumno.setCorreo(rs.getString("correo"));
			alumno.setNota1(rs.getDouble("nota1"));
			alumno.setNota2(rs.getDouble("nota2"));
			alumno.setNota3(rs.getDouble("nota3"));
			alumno.setNota_socio(rs.getDouble("nota_socio"));
			alumno.setAuto_evaluacion(rs.getDouble("auto_evaluacion"));
			alumno.setNota_final(rs.getDouble("nota_final"));
			alumno.setEliminado(rs.getBoolean("eliminado"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_AlumnoTO : " + e.getMessage());
		}
		
		return alumno;
	}
	
	public int insertarAlumno(AlumnoTO alumno){
		PreparedStatement pstmt = null;
		int result = -1;
		
		String query = " INSERT INTO alumno( "+
					   " matricula, id_curso, nombre, apellido, nota1, "+
					   " nota2, nota3, nota_socio, auto_evaluacion, nota_final, eliminado) "+
					   " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";	
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, alumno.getMatricula());
			pstmt.setInt(2, alumno.getId_curso());
			pstmt.setString(3, alumno.getNombre());
			pstmt.setString(4, alumno.getApellido());
			pstmt.setDouble(5, alumno.getNota1());
			pstmt.setDouble(6, alumno.getNota2());
			pstmt.setDouble(7, alumno.getNota3());
			pstmt.setDouble(8, alumno.getNota_socio());
			pstmt.setDouble(9, alumno.getNota_final());
			pstmt.setDouble(10, alumno.getAuto_evaluacion());
			pstmt.setBoolean(11, alumno.getEliminado());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> insertarAlumno -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	public int modificarAlumno(AlumnoTO alumno){
			
		return 0;
	}
	public int eliminarAlumno(AlumnoTO alumno){
		PreparedStatement pstmt = null;
		int result = -1;
		
		String query = " DELETE FROM alumno "+
					   " WHERE matricula=? AND id_curso=?";	
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, alumno.getMatricula());
			pstmt.setInt(2, alumno.getId_curso());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> eliminarAlumno -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	public int modificarPerfilAlumno(AlumnoTO alumno){
		PreparedStatement pstmt = null;
		int result = -1;
		
		String query =  " UPDATE alumno " +
						" SET tipo_fono=?, fono=?, correo=? " +
						" WHERE matricula=? AND id_curso=?;";
		
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setString(1, alumno.getTipo_fono());
			pstmt.setInt(2, alumno.getFono());
			pstmt.setString(3, alumno.getCorreo());
			pstmt.setInt(4, alumno.getMatricula());
			pstmt.setInt(5, alumno.getId_curso());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> modificarPerfilAlumno -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	public int asignarAlumnoGae(GaeTO gae, AlumnoTO alumno){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, alumno.getMatricula());
			ps.setInt(3, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> asignarAlumnoGae -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int asignarAlumnoRepresentante(GaeTO gae){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=?, rut_gae=?, tipo_fono=?, fono=?, correo=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, gae.getRut_gae());
			ps.setString(3, gae.getTipo_fono());
			ps.setInt(4, gae.getFono());
			ps.setString(5, gae.getCorreo());
			ps.setInt(6, gae.getMatriculaRepresentante());
			ps.setInt(7, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> asignarAlumnoRepresentant -> "+ ex.getMessage());
		}		
		return result;
	}

	public List<AlumnoTO> obtenerAlumnosCurso(CursoTO curso)
	{
		List<AlumnoTO> alumnosCurso = new ArrayList<AlumnoTO>();
		
		String query = " SELECT "+ CAMPOS_ALUMNO +
					   " FROM alumno WHERE id_curso = ? " +
					   " ORDER BY apellido";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				alumnosCurso.add(this.resultSet_a_AlumnoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerAlumnosCurso : " + sqle.getMessage());
		}
		return alumnosCurso;
	}
	
	public List<AlumnoTO> obtenerAlumnosGae(GaeTO gae)
	{
		List<AlumnoTO> alumnosGae = new ArrayList<AlumnoTO>();
		
		String query = " SELECT "+ CAMPOS_ALUMNO +
					   " FROM alumno WHERE id_gae = ? " +
					   " ORDER BY apellido";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, gae.getId_gae());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				alumnosGae.add(this.resultSet_a_AlumnoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerAlumnosGae : " + sqle.getMessage());
		}
		return alumnosGae;
	}
	

	public int crearAlumnoCurso(AlumnoTO alumno)
	{
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO alumno( "+
					   "matricula, id_curso, nombre, apellido, nota1, "+
					   "nota2, nota3, nota_socio, auto_evaluacion, nota_final, eliminado)"+
					   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";	
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, alumno.getMatricula());
			ps.setInt(2, alumno.getId_curso());
			ps.setString(3, alumno.getNombre());
			ps.setString(4, alumno.getApellido());
			ps.setDouble(5, alumno.getNota1());
			ps.setDouble(6, alumno.getNota2());
			ps.setDouble(7, alumno.getNota3());
			ps.setDouble(8, alumno.getNota_socio());
			ps.setDouble(9, alumno.getNota_final());
			ps.setDouble(10, alumno.getAuto_evaluacion());
			ps.setBoolean(11, alumno.getEliminado());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase AlumnoDB -> crearAlumnoCurso -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int eliminarAlumnosCurso(CursoTO curso) {
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "DELETE FROM alumno WHERE id_curso = ?;";
		
		try{
			ps = this.conn.prepareStatement(query);
			ps.setInt(1, curso.getId_curso());
			result = ps.executeUpdate();
			ps.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase AlumnoDB -> eliminarAlumnosCurso -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public int asignarNotaProducto(double nota, int id_gae, int rut_gae, int numero_producto)
	{
		PreparedStatement pstm = null;
		int result = 0;
		
		String query1 = " UPDATE alumno "+
		   			   " SET nota1=? "+
				       " WHERE id_gae=?; ";
		
		String query2 = " UPDATE alumno "+
					   " SET nota2=? "+
				       " WHERE id_gae=?; ";
		
		String query3 = " UPDATE alumno "+
					   " SET nota3=? "+
				       " WHERE id_gae=?; ";
		
		try{
			
			if(numero_producto==1)
				pstm = this.conn.prepareStatement(query1);
			else
				if(numero_producto==2)
					pstm = this.conn.prepareStatement(query2);
				else
					if(numero_producto==3)
						pstm = this.conn.prepareStatement(query3);			
			
			pstm.setDouble(1, nota);
			pstm.setInt(2, id_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase AlumnoDB -> asignarNotaProducto -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public int asignarNotaSocio(double promedioPautasGae, int id_gae, int rut_gae)
	{
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE alumno "+
		   			   " SET nota_socio=? "+
				       " WHERE id_gae=?; ";
		
		try{
			pstm = this.conn.prepareStatement(query);		
			
			pstm.setDouble(1, promedioPautasGae);
			pstm.setInt(2, id_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase AlumnoDB -> asignarNotaSocio -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public int asignarNotaFinal(int id_gae, int rut_gae){
		PreparedStatement pstm = null;
		int result = 0;
		
		String query = " UPDATE alumno "+
		   			   " SET nota_final = ROUND ((nota1+nota2+nota3+nota_socio+auto_evaluacion)/5,2) "+
				       " WHERE id_gae = ?; ";
		
		try{
			
			pstm = this.conn.prepareStatement(query);		
			
			pstm.setDouble(1, id_gae);
			
			result = pstm.executeUpdate();
			
			pstm.close();
			
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase AlumnoDB -> asignarNotaFinal -> "+ ex.getMessage());
		}		
		return result;
	}
	
	@Override
	public List<Double> obtenerPromedioAlumno(int id_gae, int rut_gae)
	{
		List<Double> resp = new ArrayList<Double>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT ROUND ((nota1+nota2+nota3+nota_socio+auto_evaluacion)/5,2) as promedio FROM alumno WHERE id_gae = ? ";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			
			pstmt.setInt(1, id_gae);
			
			rs = pstmt.executeQuery();
			
			while(rs.next())
				resp.add(rs.getDouble("promedio"));
			
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en AlumnoDB -> obtenerPromedioAlumno() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return resp;
	}
	
	@Override
	public int autoevaluarAlumnoGae(AlumnoTO alumno)
	{
		PreparedStatement pstmt = null;
		int result = -1;
		
		String query =  " UPDATE alumno " +
						" SET auto_evaluacion=? " +
						" WHERE matricula=? AND id_curso=?;";
		
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setDouble(1, alumno.getAuto_evaluacion());
			pstmt.setInt(2, alumno.getMatricula());
			pstmt.setInt(3, alumno.getId_curso());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase AlumnoDB -> autoevaluarAlumnoGae -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
}
